﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_MNY_REPORT_Mny3013p_Get_Money_Card_Detail_List_By_Account')
	BEGIN
		DROP Procedure usp_UPDMS_MNY_REPORT_Mny3013p_Get_Money_Card_Detail_List_By_Account
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_MNY_REPORT_Mny3013p_Get_Money_Card_Detail_List_By_Account
**	Desc : 연간출납분석표에서 금액 누르면 상세 항목을 보여준다
**	Test Exec Query : Exec usp_UPDMS_MNY_REPORT_Mny3013p_Get_Money_Card_Detail_List_By_Account '2010-06', 'AC010','ko'
**	Called by : Mny_Dac_UPDMS_MNY_REPORT_Mny3013p.cs
**	Program ID : Mny3013p
**	Auth : 송시명
**	Date : 2010-06-21
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_MNY_REPORT_Mny3013p_Get_Money_Card_Detail_List_By_Account]
@ls_account nvarchar(5),
@ls_base_y_m nvarchar(7),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT Account,
       dbo.ufn_UPDMS_Get_Code_Name(rslt.Account, 'MNY', @ls_lang_set) AS Account_Nm,
       Div,
       dbo.ufn_UPDMS_Get_Code_Name(rslt.Div, 'MNY', @ls_lang_set) AS Div_Nm,
       Card_Nm,
       Reg_Dt,
       Contents,
       CASE WHEN LEN(rslt.Remark) > 16 THEN '<span title="' + Remark + '">' + dbo.ufn_UPDMS_Reduce_Text(rslt.Remark, 16) + '</span>'
            ELSE Remark
       END Remark,
	   Remark AS Real_Remark,
       Amount
  FROM (
       --현금
       SELECT umiom.Account,
              'AH002' AS Div,
              '' AS Card_Nm,
              umiom.Reg_Dt,
              umiom.Contents,
              umiom.Remark,
              umiom.Amount
         FROM UPDMS_MNY_IN_OUT_MGT AS umiom WITH(NOLOCK)
        WHERE LEFT(umiom.Reg_Dt, 7) = @ls_base_y_m
          AND umiom.Account = @ls_account
       UNION ALL
       --카드
       SELECT x.Account,
              'AH001' AS Div,
              uci.Card_Nm,
              Biz_Dt,
              Contents,
              Remark,
              Amount
         FROM UPDMS_CRD_INFO AS uci WITH(NOLOCK)
         JOIN (
              SELECT Account,
                     Card_Cd,
                     Biz_Dt,
                     Contents,
                     Remark,
                     Amount
                FROM UPDMS_CRD_ITEM_BIZ WITH(NOLOCK)
               WHERE LEFT(Paymnt_Dt, 7) = @ls_base_y_m
                 AND Account = @ls_account
              UNION ALL
              SELECT b.Account,
                     b.Card_Cd,
                     b.Biz_Dt,
                     b.Contents,
                     b.Remark,
                     a.Ins_Month_Amt
               FROM UPDMS_CRD_INSTALLMENT_REPAY AS a WITH(NOLOCK)
               JOIN UPDMS_CRD_INSTALLMENT_BIZ AS b WITH(NOLOCK) ON a.Ins_Cd = b.Ins_Cd
              WHERE LEFT(a.Paymnt_Dt, 7) = @ls_base_y_m
                AND b.Account = @ls_account
              ) x
           ON uci.Card_Cd = x.Card_Cd
       ) rslt
 ORDER BY rslt.Div, rslt.Reg_Dt

GO